package app.beit.fintrack.dao;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import app.beit.fintrack.entity.Hospital;
import app.beit.fintrack.entity.Province;
import app.beit.fintrack.entity.Zone;

public class MasterDao extends BaseDao {
	
	private final Logger log = Logger.getLogger(this.getClass());
	
	@SuppressWarnings("unchecked")
	public List<Zone> getZone() throws Exception{
		this.log.info("get zones");
		final StringBuilder sb = new StringBuilder();
		sb.append("select p.nhso_zone as zoneId, p.nhso_zonename as zoneName from province p ");
		sb.append("where p.nhso_zone < 14 and used = 'Y' ");
		sb.append("group by p.nhso_zone, p.nhso_zonename ");
		sb.append("order by p.nhso_zone ");
		this.log.info("get zones with sql query : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Zone.class);
		final List<Zone> results = sql.list();
		session.close();
		this.log.info("end get zones...");
		return results;
	}
	public Zone getZoneByZoneId(final String zoneId) throws Exception{
		this.log.info("get zones by zone id");
		final StringBuilder sb = new StringBuilder();
		sb.append("select p.nhso_zone as zoneId, p.nhso_zonename as zoneName from province p ");
		sb.append("where p.nhso_zone=").append(zoneId).append(" and used = 'Y' ");
		this.log.info("get zones with sql query : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Zone.class);
		final Zone results = (Zone) sql.uniqueResult();
		session.close();
		this.log.info("end get zones by zone id...");
		return results;
	}
	
	@SuppressWarnings("unchecked")
	public List<Hospital> getHospitalByZoneIdAndProvinceId(final String zoneId, final String provinceId) throws Exception{
		this.log.info("get all hospital in zone : " + zoneId + " and province : " + provinceId);
		final StringBuilder sb = new StringBuilder();
		sb.append("select lfa1.vndr_no as hospitalCode,(case when (trim(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) is not null) then(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) else (wel2.h_nm) end) as hospitalName ");
		sb.append("from ODS_DW_LFA1 lfa1, ODS_DW_ZHOSPITAL_WEL2 wel2 where lfa1.vndr_no = wel2.h_cd and nhso_zone_id not in ('14', '15', '99') ");
		sb.append("and wel2.used_data = 'Y' AND SUBSTR (TRIM (lfa1.vndr_no), 1, 1) NOT IN ('E', 'A', 'P') and wel2.prvn_id = '"+provinceId+"' ");
		sb.append("order by h_cd ");
		this.log.info("get all hospital in zone and province with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Hospital.class);
		final List<Hospital> results = sql.list();
		session.close();
		this.log.info("end get all hospital in zone and province.........");
		return results;
	}
	
	@SuppressWarnings("unchecked")
	public List<Hospital> getAllHospital() throws Exception{
		this.log.info("get all hospital");
		final StringBuilder sb = new StringBuilder();
		sb.append("select lfa1.vndr_no as hospitalCode,(case when (trim(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) is not null) then(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) else (wel2.h_nm) end) as hospitalName ");
		sb.append("from ODS_DW_LFA1 lfa1, ODS_DW_ZHOSPITAL_WEL2 wel2 where lfa1.vndr_no = wel2.h_cd and nhso_zone_id not in ('14', '15', '99') ");
		sb.append("and wel2.used_data = 'Y' AND SUBSTR (TRIM (lfa1.vndr_no), 1, 1) NOT IN ('E', 'A', 'P') ");
		sb.append("order by h_cd ");
		this.log.info("get all hospital with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Hospital.class);
		final List<Hospital> results = sql.list();
		session.close();
		this.log.info("end get all hospital .........");
		return results;
	}

	@SuppressWarnings("unchecked")
	public List<Province> getProvinceByZoneId(final String zoneId) throws Exception{
		this.log.info("get all province in zone : " + zoneId);
		final StringBuilder sb = new StringBuilder();
		sb.append("select t.province_id as provinceCode, t.province_name as provinceName ");
		sb.append("from PROVINCE t ");
		sb.append("where t.nhso_zone='").append(zoneId).append("' and used = 'Y' ");
		sb.append("order by t.nhso_zone ");
		this.log.info("get all province in zone with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Province.class);
		final List<Province> results = sql.list();
		session.close();
		this.log.info("end get all province in zone.........");
		return results;
	}
	
	public Hospital getHospitalByHCode(String hCode) throws Exception {
		this.log.info("get hospital by hospital code : " + hCode);

		final StringBuilder sb = new StringBuilder();
		sb.append("select lfa1.vndr_no as hospitalCode,(case when (trim(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) is not null) then(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) else (wel2.h_nm) end) as hospitalName ");
		sb.append("from ODS_DW_LFA1 lfa1, ODS_DW_ZHOSPITAL_WEL2 wel2 where lfa1.vndr_no = wel2.h_cd and nhso_zone_id not in ('14', '15', '99') ");
		sb.append("and wel2.used_data = 'Y' AND SUBSTR (TRIM (lfa1.vndr_no), 1, 1) NOT IN ('E', 'A', 'P') and lfa1.vndr_no like '%"+hCode+"' ");
		sb.append("order by h_cd ");
		this.log.info("get hospital by h_code with sql query : " + sb.toString());
		
//		final Hospital result = (Hospital) jdbcTemplate.queryForObject(
//				                sb.toString(), new BeanPropertyRowMapper(Hospital.class));

		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Hospital.class);
		final Hospital result = (Hospital) sql.uniqueResult();
		session.close();
		this.log.info("end get hospital by h_code........");
		return result;
	}

	public Province getProvinceByProvinceId(final String provinceId) throws Exception{
		this.log.info("get province by province id : " + provinceId);
		final StringBuilder sb = new StringBuilder();
		sb.append("select t.province_id as provinceCode, t.province_name as provinceName ");
		sb.append("from PROVINCE t ");
		sb.append("where t.province_id='").append(provinceId).append("' ");
		this.log.info("get province by province id with sql query : " + sb.toString());
		
//		final Province result = (Province) jdbcTemplate.queryForObject(
//				                sb.toString(), new BeanPropertyRowMapper(Province.class));
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Province.class);
		final Province result = (Province) sql.uniqueResult();
		session.close();
		this.log.info("end get province by province id.......");
		return result;
	}

	public Province getProvinceByHCode(final String hospitalCode) throws Exception{
		this.log.info("get province by hospital id : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		sb.append("select p.province_id as provinceCode, p.province_name as provinceName ");
		sb.append("from ods_dw_zhospital_wel2 h, province p ");
		sb.append("where h.h_cd like '%").append(hospitalCode).append("' ");
		sb.append("and h.org_type='H' ");
		sb.append("and p.nhso_zone between 1 and 13 ");
		sb.append("and h.prvn_id=p.province_id ");
		this.log.info("get province by hospital id with sql query : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Province.class);
		final Province result = (Province) sql.uniqueResult();
		session.close();
		this.log.info("end get province by hospital id.......");
		return result;
	}

	public Zone getZoneByHCode(final String hospitalCode) throws Exception{
		this.log.info("get zone by hospital id : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		sb.append("select p.nhso_zone as zoneId, p.nhso_zonename as zoneName ");
		sb.append("from ods_dw_zhospital_wel2 h, province p ");
		sb.append("where h.h_cd like '%").append(hospitalCode).append("' ");
		sb.append("and h.org_type='H' ");
		sb.append("and p.nhso_zone between 1 and 13 ");
		sb.append("and h.prvn_id=p.province_id ");
		this.log.info("get zone by hospital id with sql query : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(Zone.class);
		final Zone result = (Zone) sql.uniqueResult();
		session.close();
		this.log.info("end get zone by hospital id.......");
		return result;
	}
}
